/****** Object:  StoredProcedure [dbo].[LeagueReport]    Script Date: 04/19/2007 22:40:16 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LeagueReport] 
@leagueID int

AS

select t.teamname,count(*)as totalplayers,Avg(Cast(Skilllevel as float)) as skilllevel,Males= SUM(Case when p.male =1 THEN 1 ELSE 0 END),MaleSkill=AVG(Case when p.male=1 then Cast(p.skilllevel as float) else null end),FeMales = SUM(CASE WHEN p.male = 0 then 1 else 0 END),FeMaleSkill=AVG(Case when p.male=0 then Cast(p.skilllevel as float) else null end)

from team t inner join 

	teamplayer tp on t.teamID = tp.teamID  inner join
	player p on p.UserId= tp.UserId 
     
where leagueID = @leagueID
group by t.teamname
order by t.teamname

 